
if exists(select name from sysobjects where name = 'sp_SinhVienSearch')
	drop proc sp_SinhVienSearch
go
/*
	Created by: Phuoc NH
	Created on: 13/05/2013 (dd/mm/yyyy)
	Description: 
	--

	File:$sql-scripts/Proc.sp_SinhVienSearch.sql
*/

create proc sp_SinhVienSearch
@MaLop varchar(50),
@MaSinhVien varchar(50),
@TenSinhVien Nvarchar(200),

 @PageIndex INT = 1,
      @PageSize INT = 10
      
as
begin
	--select MatKhau, DiaChiHT, Email, DienThoai, MaSinhVien,Ho, Ten, MaLop, CONVERT(varchar(12), NgaySinh, 103) as ngaysinh, GioiTinh, DanToc, TonGiao, CMTND, CONVERT(varchar(12), NgaySinh, 103) as ngaycap, NoiCap, HoKhau, NoiSinh, HoTenBo, NNBo, NNMe, HoTenMe, YEAR( NamTN12) as namtn12, SoHieuBang12, DiaChi  from SinhVien where MaSinhVien=@MaSinhVien and TrangThai<>'0'
	 SET NOCOUNT ON;
      SELECT ROW_NUMBER() OVER
      (
            ORDER BY a.masinhvien ASC
      )AS RowNumber,  MatKhau, DiaChiHT, Email, DienThoai, MaSinhVien,Ho, Ten, a.MaLop, CONVERT(varchar(12), NgaySinh, 103) as ngaysinh, GioiTinh, DanToc, TonGiao, CMTND, CONVERT(varchar(12), ngaycap, 103) as ngaycap, NoiCap, HoKhau, NoiSinh, HoTenBo, NNBo, NNMe, HoTenMe, YEAR( NamTN12) as namtn12, SoHieuBang12, DiaChi , e.MaKhoa 
     INTO #Results
      FROM  SinhVien a inner join Lop b on a.MaLop=b.MaLop and b.MaLop like '%'+@MaLop+'%' and (a.Ho+' '+a.Ten) like '%'+@TenSinhVien+'%' and a.MaSinhVien like '%'+@MaSinhVien+'%' and a.TrangThai<>'0' inner join (ChuyenNganh c inner join nganh d on c.manganh=d.manganh inner join Khoa e on d.makhoa=e.MaKhoa) on b.MaCN=c.MaCN order by MaSinhVien
      
      declare @Record int
           set @Record = (SELECT COUNT(*) FROM #Results)
      SELECT *,@Record  AS Record, RowNumber AS RowNumber FROM #Results
      WHERE RowNumber BETWEEN(@PageIndex -1) * @PageSize + 1 AND(((@PageIndex -1) * @PageSize + 1) + @PageSize) - 1
end

--select top 100 * from SinhVien where MaSinhVien like'51%'

